REM  SQL scripts for Appendix A of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.


> ORACLE_SID=olddb; export ORACLE_SID
> ORAENV_ASK=NO; export ORAENV_ASK
> . oraenv
> exp system/manager file=olddb.dmp rows=Y grants=Y indexes=Y

> ORACLE_SID=newdb; export ORACLE_SID
> ORAENV_ASK=NO; export ORAENV_ASK
> . oraenv
> imp system/manager file=olddb.dmp full=Y buffer=1000000 commit=Y ignore=Y


> imp system/manager file=olddb.dmp full=Y buffer=1000000 commit=Y ignore=Y rows=Y indexes=N
> imp system/manager file=olddb.dmp full=Y ignore=Y rows=N indexes=Y

> svrmgrl
SVRMGR> connect internal;
SVRMGR> @catalog



> svrmgrl
SVRMGR> startup nomount;
SVRMGR> alter database convert;
SVRMGR> alter database open resetlogs;

> svrmgrl
SVRMGR> connect internal
SVRMGR> @cat8000.sql

select RowID
  from NUMBERTEST
 where Rownum=1;

select DBMS_ROWID.ROWID_OBJECT(RowID)
  from NUMBERTEST
 where RowNum=1;

select Object_Name
  from DBA_OBJECTS
 where Object_ID = 2783;

select DBMS_ROWID.ROWID_TO_RESTRICTED(RowID,0)
  from NUMBERTEST;

select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID)
  from NUMBERTEST
 where RowNum=1;

select File_Name, File_ID
  from DBA_DATA_FILES
 where Relative_FNO = 80;

select DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(RowID,'TALBOT','NUMBERTEST')
  from NUMBERTEST
 where RowNum=1;

select DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
  from NUMBERTEST
 where RowNum=1;

select Relative_FNO, Block_ID
  from DBA_EXTENTS
 where Segment_Name = 'NUMBERTEST';

select DBMS_ROWID.ROWID_RELATIVE_FNO(RowID),
       DBMS_ROWID.ROWID_BLOCK_NUMBER(RowID)
  from NUMBERTEST
 where RowNum=1;

select DBMS_ROWID.ROWID_ROW_NUMBER(RowID)
  from NUMBERTEST;




create type ADDRESS_TY as object
(Street  VARCHAR2(50),
 City    VARCHAR2(25),
 State   CHAR(2),
 Zip     NUMBER);
create type PERSON_TY as object
(Name      VARCHAR2(25),
 Address   ADDRESS_TY);

create table CUSTOMER
(Customer_ID  NUMBER,
 Person       PERSON_TY);
insert into CUSTOMER values
(444, 
 PERSON_TY('JANET NORWOOD',
    ADDRESS_TY('100 RIVER RD', 'RIDGE', 'MA', '10002')));

create type PERSON_TY as object
(Name     VARCHAR2(25),
 Address  ADDRESS_TY);

grant EXECUTE on ADDRESS_TY to George;

create or replace type PERSON_TY as object
(Name     VARCHAR2(25),
 Address  Dora.ADDRESS_TY);
create table CUSTOMER
(Customer_ID  NUMBER,
 Person       PERSON_TY);
insert into CUSTOMER values
(1,PERSON_TY('SomeName',
   ADDRESS_TY('StreetValue','CityValue','ST',11111)))
insert into CUSTOMER values
(1,PERSON_TY('SomeName',
   Dora.ADDRESS_TY('StreetValue','CityValue','ST',11111)))

create table CUSTOMER
(Customer_ID    NUMBER,
 Person         PERSON_TY);
select Customer_ID, Person.Name
  from CUSTOMER;
select Person.Name,
       Person.Address.City
  from CUSTOMER
 where Person.Address.City like 'F%';

create index I_CUSTOMER$CITY
on CUSTOMER(Person.Address.City);


create table CUSTOMER
(Customer_ID NUMBER   primary key,
 Name        VARCHAR2(25),
 Street      VARCHAR2(50),
 City        VARCHAR2(25),
 State       CHAR(2),
 Zip         NUMBER);

create view CUSTOMER_OV (Customer_ID, Person) as
select Customer_ID,
       PERSON_TY(Name,
       ADDRESS_TY(Street, City, State, Zip))
  from CUSTOMER;



create or replace type CUSTOMER_TY as object
(Customer_ID NUMBER,
 Name        VARCHAR2(25),
 Street      VARCHAR2(50),
 City        VARCHAR2(25),
 State       CHAR(2),
 Zip         NUMBER);

create view CUSTOMER_OV of CUSTOMER_TY
with object OID (Customer_ID) as
select Customer_ID, Name, Street, City, State, Zip
  from CUSTOMER;

create table CUSTOMER_CALL
(Customer_ID   NUMBER,
 Call_Number   NUMBER,
 Call_Date     DATE,
 constraint CUSTOMER_CALL_PK 
     primary key (Customer_ID, Call_Number),
 constraint CUSTOMER_CALL_FK foreign key (Customer_ID)
    references CUSTOMER(Customer_ID));

create view CUSTOMER_CALL_OV as
select MAKE_REF(CUSTOMER_OV, Customer_ID) Customer_ID,
       Call_Number,
       Call_Date
  from CUSTOMER_CALL;

select DEREF(CCOV.Customer_ID)
  from CUSTOMER_CALL_OV CCOV
 where Call_Date = TRUNC(SysDate);



